﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DesignLayOut_QLQTPM_Team7.DAO;

namespace DesignLayOut_QLQTPM_Team7
{
    class XuLy
    {

        public static DataTable TraCuuTheoKhuVuc(int matinhthanh, int maquanhuyen, int maphuong, int maduong)
        {
            string sql = "select dl.MaDuLieu, dl.MaDichVu, dv.TenDichVu, dl.MaTenDiaDiem, tdd.TenDiaDiem, dl.SoNha, dl.MaDuong, d.TenDuong, dl.MaPhuong, p.TenPhuong, dl.MaQuanHuyen, qh.TenQuanHuyen, dl.MaTinhThanh, tt.TenTinhThanh, dl.KinhDo, dl.ViDo, dl.ChuThich from DULIEU dl, TENDIADIEM tdd, PHUONG p, DUONG d, QUANHUYEN qh, TINHTHANH tt, DICHVU dv where dl.MaTenDiaDiem=tdd.MaTenDiaDiem and dl.MaDichVu=dv.MaDichVu and dl.MaDuong=d.MaDuong and dl.MaPhuong=p.MaPhuong and dl.MaQuanHuyen=qh.MaQuanHuyen and dl.MaTinhThanh=tt.MaTinhThanh";
            if (matinhthanh != -1)
            {
                sql += " and tt.MaTinhThanh=" + matinhthanh;
            }
            if (maquanhuyen != -1)
            {
                sql += " and qh.MaQuanHuyen=" + maquanhuyen;
            }
            if (maphuong != -1)
            {
                sql += " and p.MaPhuong=" + maphuong;
            }
            if (maduong != -1)
            {
                sql += " and d.MaDuong=" + maduong;
            }
            DataProvider dtb = new DataProvider();
            return dtb.executeQuery(sql);
        }

        public static DataTable TraCuuTheoDichVu(int madichvu)
        {
            string sql = "select dl.MaDuLieu, dl.MaDichVu, dv.TenDichVu, dl.MaTenDiaDiem, tdd.TenDiaDiem, dl.SoNha, dl.MaDuong, d.TenDuong, dl.MaPhuong, p.TenPhuong, dl.MaQuanHuyen, qh.TenQuanHuyen, dl.MaTinhThanh, tt.TenTinhThanh, dl.KinhDo, dl.ViDo, dl.ChuThich from DULIEU dl, TENDIADIEM tdd, PHUONG p, DUONG d, QUANHUYEN qh, TINHTHANH tt, DICHVU dv where dl.MaTenDiaDiem=tdd.MaTenDiaDiem and dl.MaDichVu=dv.MaDichVu and dl.MaDuong=d.MaDuong and dl.MaPhuong=p.MaPhuong and dl.MaQuanHuyen=qh.MaQuanHuyen and dl.MaTinhThanh=tt.MaTinhThanh";
            if (madichvu != -1)
            {
                sql += " and dv.MaDichVu=" + madichvu;
            }
            DataProvider dtb = new DataProvider();
            return dtb.executeQuery(sql);
        }

        


        public static void SuaDichVu(int madiadiem, string tendiadiem, string sonha, int maduong, int maphuong, int maquan, int matinhthanh, int madichvu, string kinhdo, string vido, string chuthich)
        {
            DataProvider dtb = new DataProvider();
            string sql = "update TENDIADIEM set ";
            sql += "TenDiaDiem ='" + tendiadiem + "'";
            sql += " where MaTenDiaDiem =" + madiadiem;

            dtb.executeNonQuery(sql);

            sql = "update DULIEU set ";
            sql += "SoNha ='" + sonha + "',";
            sql += "MaDuong =" + maduong + ",";
            sql += "MaPhuong =" + maphuong + ",";
            sql += "MaQuanHuyen =" + maquan + ",";
            sql += "MaTinhThanh =" + matinhthanh + ",";
            sql += "MaDichVu =" + madichvu + ",";
            sql += "KinhDo ='" + kinhdo + "',";
            sql += "ViDo ='" + vido + "',";
            sql += "ChuThich ='" + chuthich + "'";
            sql += " where MaTenDiaDiem =" + madiadiem;

            dtb.executeNonQuery(sql);
        }

    }
}
